﻿// Type: GandA.Component.DataAccess.SQLServer.SQL
// Assembly: GandA.Component.DataAccess, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
// Assembly location: C:\CodePlex\CLab\CLab\DLLs\GandA.Component.DataAccess.dll

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace GandA.Component.DataAccess.SQLServer
{
    public class SQL
    {
        private static string sConnectionString = string.Empty;
        private static SqlTransaction oTransaction;
        private static SqlConnection oConnection;
        private static SqlCommand oCommand;
        private static SqlDataReader oDataReader;
        private static SqlParameter oPr;

        static SQL()
        {
        }

        public SQL()
        {
            GandA.Component.DataAccess.SQLServer.SQL.sConnectionString = this.ConnectionString();
            GandA.Component.DataAccess.SQLServer.SQL.oTransaction = (SqlTransaction)null;
            GandA.Component.DataAccess.SQLServer.SQL.oConnection = (SqlConnection)null;
            GandA.Component.DataAccess.SQLServer.SQL.oCommand = (SqlCommand)null;
            GandA.Component.DataAccess.SQLServer.SQL.oDataReader = (SqlDataReader)null;
            GandA.Component.DataAccess.SQLServer.SQL.oPr = (SqlParameter)null;
        }

        public SqlDataReader ExecuteReaderProcedure(string pSPName)
        {
            return this.ExecuteReaderProcedure(pSPName, (SQLParameterCollection)null);
        }

        public SqlDataReader ExecuteReaderProcedure(string pSPName, SQLParameterCollection pParameterCollection)
        {
            string str = string.Empty;
            try
            {
                this.OpenConnection();
                GandA.Component.DataAccess.SQLServer.SQL.oCommand = new SqlCommand(pSPName, GandA.Component.DataAccess.SQLServer.SQL.oConnection);
                GandA.Component.DataAccess.SQLServer.SQL.oCommand.CommandType = CommandType.StoredProcedure;
                if (pParameterCollection != null)
                {
                    foreach (SQLParameter sqlParameter in (CollectionBase)pParameterCollection)
                    {
                        GandA.Component.DataAccess.SQLServer.SQL.oPr = new SqlParameter();
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.ParameterName = sqlParameter.ParamName;
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.Value = sqlParameter.ParamValue;
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.Direction = (ParameterDirection)sqlParameter.EnumParameterDirection;
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.SqlDbType = (SqlDbType)sqlParameter.EnumSqlDbType;
                        GandA.Component.DataAccess.SQLServer.SQL.oCommand.Parameters.Add(GandA.Component.DataAccess.SQLServer.SQL.oPr);
                    }
                }
                GandA.Component.DataAccess.SQLServer.SQL.oDataReader = GandA.Component.DataAccess.SQLServer.SQL.oCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException ex)
            {
                throw new SQLException(ex.Number < 50000 ? "Error en acceso a datos" : ex.Message, (Exception)ex);
            }
            catch (Exception ex)
            {
                throw new SQLException("Error en la aplicación", ex);
            }
            finally
            {
                if (GandA.Component.DataAccess.SQLServer.SQL.oCommand != null)
                {
                    GandA.Component.DataAccess.SQLServer.SQL.oCommand.Dispose();
                    GandA.Component.DataAccess.SQLServer.SQL.oCommand = (SqlCommand)null;
                }
            }
            return GandA.Component.DataAccess.SQLServer.SQL.oDataReader;
        }

        public void ExecuteNonQueryStoreProcedure(string pSPName)
        {
            this.ExecuteNonQueryStoreProcedure(pSPName, (SQLParameterCollection)null, true);
        }

        public void ExecuteNonQueryStoreProcedure(string pSPName, SQLParameterCollection pParameterCollection)
        {
            this.ExecuteNonQueryStoreProcedure(pSPName, pParameterCollection, true);
        }

        public void ExecuteNonQueryStoreProcedure(string pSPName, bool isNormalTransaction)
        {
            this.ExecuteNonQueryStoreProcedure(pSPName, (SQLParameterCollection)null, isNormalTransaction);
        }

        public void ExecuteNonQueryStoreProcedure(string pSPName, SQLParameterCollection pParameterCollection, bool isNormalTransaction)
        {
            string str = string.Empty;
            try
            {
                this.OpenConnection();
                if (GandA.Component.DataAccess.SQLServer.SQL.oTransaction == null && isNormalTransaction)
                    this.BeginTransaction();
                GandA.Component.DataAccess.SQLServer.SQL.oCommand = new SqlCommand(pSPName, GandA.Component.DataAccess.SQLServer.SQL.oConnection, GandA.Component.DataAccess.SQLServer.SQL.oTransaction);
                GandA.Component.DataAccess.SQLServer.SQL.oCommand.CommandType = CommandType.StoredProcedure;
                if (pParameterCollection != null)
                {
                    foreach (SQLParameter sqlParameter in (CollectionBase)pParameterCollection)
                    {
                        GandA.Component.DataAccess.SQLServer.SQL.oPr = new SqlParameter();
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.ParameterName = sqlParameter.ParamName;
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.Value = sqlParameter.ParamValue;
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.Direction = (ParameterDirection)sqlParameter.EnumParameterDirection;
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.SqlDbType = (SqlDbType)sqlParameter.EnumSqlDbType;
                        GandA.Component.DataAccess.SQLServer.SQL.oCommand.Parameters.Add(GandA.Component.DataAccess.SQLServer.SQL.oPr);
                    }
                }
                GandA.Component.DataAccess.SQLServer.SQL.oCommand.ExecuteNonQuery();
                if (isNormalTransaction)
                    this.CommitTransaction();
                this.CloseConnection();
            }
            catch (SqlException ex)
            {
                if (GandA.Component.DataAccess.SQLServer.SQL.oTransaction != null && isNormalTransaction)
                    this.RollbackTransaction();
                throw new SQLException(ex.Number < 50000 ? "Error en acceso a datos" : ex.Message, (Exception)ex);
            }
            catch (Exception ex)
            {
                throw new SQLException("Error en la aplicación", ex);
            }
            finally
            {
                this.CloseConnection();
                if (GandA.Component.DataAccess.SQLServer.SQL.oCommand != null)
                {
                    GandA.Component.DataAccess.SQLServer.SQL.oCommand.Dispose();
                    GandA.Component.DataAccess.SQLServer.SQL.oCommand = (SqlCommand)null;
                }
            }
        }

        public DataSet ExecuteQueryStoreProcedure(string pSPName)
        {
            return this.ExecuteQueryStoreProcedure(pSPName, (SQLParameterCollection)null, true, false);
        }

        public DataSet ExecuteQueryStoreProcedure(string pSPName, SQLParameterCollection pParameterCollection)
        {
            return this.ExecuteQueryStoreProcedure(pSPName, pParameterCollection, true, false);
        }

        public void ExecuteQueryStoreProcedure(string pSPName, bool isNormalTransaction)
        {
            this.ExecuteQueryStoreProcedure(pSPName, (SQLParameterCollection)null, isNormalTransaction, false);
        }

        public DataSet ExecuteQueryStoreProcedure(string pSPName, SQLParameterCollection pParameterCollection, bool isNormalTransaction, bool pConsulta)
        {
            string str = string.Empty;
            try
            {
                this.OpenConnection();
                if (!pConsulta)
                {
                    if (GandA.Component.DataAccess.SQLServer.SQL.oTransaction == null && isNormalTransaction)
                        this.BeginTransaction();
                    GandA.Component.DataAccess.SQLServer.SQL.oCommand = new SqlCommand(pSPName, GandA.Component.DataAccess.SQLServer.SQL.oConnection, GandA.Component.DataAccess.SQLServer.SQL.oTransaction);
                }
                else
                    GandA.Component.DataAccess.SQLServer.SQL.oCommand = new SqlCommand(pSPName, GandA.Component.DataAccess.SQLServer.SQL.oConnection);
                GandA.Component.DataAccess.SQLServer.SQL.oCommand.CommandType = CommandType.StoredProcedure;
                if (pParameterCollection != null)
                {
                    foreach (SQLParameter sqlParameter in (CollectionBase)pParameterCollection)
                    {
                        GandA.Component.DataAccess.SQLServer.SQL.oPr = new SqlParameter();
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.ParameterName = sqlParameter.ParamName;
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.Value = sqlParameter.ParamValue;
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.Direction = (ParameterDirection)sqlParameter.EnumParameterDirection;
                        GandA.Component.DataAccess.SQLServer.SQL.oPr.SqlDbType = (SqlDbType)sqlParameter.EnumSqlDbType;
                        GandA.Component.DataAccess.SQLServer.SQL.oCommand.Parameters.Add(GandA.Component.DataAccess.SQLServer.SQL.oPr);
                    }
                }
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                DataSet dataSet = new DataSet();
                sqlDataAdapter.SelectCommand = GandA.Component.DataAccess.SQLServer.SQL.oCommand;
                ((DataAdapter)sqlDataAdapter).Fill(dataSet);
                if (!pConsulta && isNormalTransaction)
                    this.CommitTransaction();
                this.CloseConnection();
                return dataSet;
            }
            catch (SqlException ex)
            {
                if (GandA.Component.DataAccess.SQLServer.SQL.oTransaction != null && isNormalTransaction)
                    this.RollbackTransaction();
                throw new SQLException(ex.Number < 50000 ? "Error en acceso a datos" : ex.Message, (Exception)ex);
            }
            catch (Exception ex)
            {
                throw new SQLException("Error en la aplicación", ex);
            }
            finally
            {
                this.CloseConnection();
                if (GandA.Component.DataAccess.SQLServer.SQL.oCommand != null)
                {
                    GandA.Component.DataAccess.SQLServer.SQL.oCommand.Dispose();
                    GandA.Component.DataAccess.SQLServer.SQL.oCommand = (SqlCommand)null;
                }
            }
        }

        public DataSet ExecuteQuerySP(string pSPName, SQLParameterCollection pParameterCollection)
        {
            return this.ExecuteQueryStoreProcedure(pSPName, pParameterCollection, true, true);
        }

        public DataSet ExecuteQuerySP(string pSPName)
        {
            return this.ExecuteQueryStoreProcedure(pSPName, (SQLParameterCollection)null, true, true);
        }

        public void OpenConnection()
        {
            GandA.Component.DataAccess.SQLServer.SQL.oConnection = new SqlConnection(GandA.Component.DataAccess.SQLServer.SQL.sConnectionString);
            GandA.Component.DataAccess.SQLServer.SQL.oConnection.Open();
        }

        public void CloseConnection()
        {
            if (GandA.Component.DataAccess.SQLServer.SQL.oConnection.State != ConnectionState.Open)
                return;
            GandA.Component.DataAccess.SQLServer.SQL.oConnection.Close();
        }

        public void BeginTransaction()
        {
            GandA.Component.DataAccess.SQLServer.SQL.oTransaction = GandA.Component.DataAccess.SQLServer.SQL.oConnection.BeginTransaction();
        }

        public void CommitTransaction()
        {
            GandA.Component.DataAccess.SQLServer.SQL.oTransaction.Commit();
            GandA.Component.DataAccess.SQLServer.SQL.oTransaction.Dispose();
            GandA.Component.DataAccess.SQLServer.SQL.oTransaction = (SqlTransaction)null;
        }

        public void RollbackTransaction()
        {
            ((DbTransaction)GandA.Component.DataAccess.SQLServer.SQL.oTransaction).Rollback();
            GandA.Component.DataAccess.SQLServer.SQL.oTransaction.Dispose();
            GandA.Component.DataAccess.SQLServer.SQL.oTransaction = (SqlTransaction)null;
        }

        private string ConnectionString()
        {
            string str = "";
            ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["BD"];
            if (connectionStringSettings.ConnectionString != null)
                str = connectionStringSettings.ConnectionString;
            return str;
        }
    }
}
